/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package battleship_server;

import java.sql.*;

/**
 *
 * @author dpo89
 */
public final class Database {

    private static final String USERNAME = "dionysus";
    private static final String PASSWORD = "delriopachecososavilla";
    private Connection con = null;

    public Database() {
        try {
            con = getConnection();
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
        }
    }

    public boolean validateLogin(String username, String password) {
        Statement stmt = null;
        String query = "SELECT * FROM APP.USERS WHERE USERNAME = \'" + username + "\'";
        try {
            stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while (rs.next()) {
                if (rs.getString("PASSWORD").equals(password)) {
                    return true;
                } else {
                    return false;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean validateLoginAdmin(String username, String password) {
        Statement stmt = null;
        String query = "SELECT * FROM APP.ADMINISTRATORS WHERE USERNAME = \'" + username + "\'";
        try {
            stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while (rs.next()) {
                if (rs.getString("PASSWORD").equals(password)) {
                    return true;
                } else {
                    return false;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public Player login(String username, String lastIPAddress) {
        Statement stmt = null;
        String query = "SELECT * FROM APP.USERS WHERE USERNAME = \'" + username + "\'";
        String query2 = "UPDATE APP.CONNECTIONS SET LASTIPADDRESS = \'" + lastIPAddress + "\' WHERE USERNAME = \'" + username + "\'";
        try {
            stmt = con.createStatement();
            stmt.executeUpdate(query2);
            ResultSet rs = stmt.executeQuery(query);
            rs.next();
            return new Player(rs.getString("NAME"), username, rs.getString("PASSWORD"), rs.getString("EMAIL"), lastIPAddress);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;

    }

    public boolean playerExists(String username) {
        Statement stmt = null;
        String query = "SELECT * FROM APP.USERS WHERE username = \'" + username + "\'";
        try {
            stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while (rs.next()) {
                if (rs.getString("USERNAME").equals(username)) {
                    return true;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                //stmt.close();
            }
            return false;
        }
    }

    public String retreiveScore(String player) {
        String result = "";
        Statement stmt = null;
        String query = "SELECT * FROM APP.SCORES WHERE USERNAME =\'" + player + "\'";
        try {
            stmt = con.createStatement();
            ResultSet resultSet = stmt.executeQuery(query);
            resultSet.next();
            int gamesPlayed = resultSet.getInt("GAMESPLAYED");
            int score = resultSet.getInt("SCORE");
            int gamesWon = resultSet.getInt("GAMESWON");
            int gamesLost = resultSet.getInt("GAMESLOST");
            result = score + "|" + gamesPlayed + "|" + gamesWon + "|" + gamesLost + "|";
        } catch (SQLException e) {
        }
        return result;
    }

    public boolean addPlayer(String name, String username, String password, String mail, String IPAddress) throws SQLIntegrityConstraintViolationException {
        Statement stmt = null;
        if (playerExists(username)) {
            return false;
        }
        String query = "INSERT INTO APP.USERS (USERNAME, PASSWORD, NAME, EMAIL) VALUES(\'" + username + "\', " + "\'" + password + "\', " + "\'" + name + "\', " + "\'" + mail + "\')";
        String query2 = "INSERT INTO APP.CONNECTIONS (USERNAME, LASTIPADDRESS) VALUES(\'" + username + "\', " + "\'" + IPAddress + "\')";
        String query3 = "INSERT INTO APP.SCORES (USERNAME, SCORE, GAMESPLAYED, GAMESWON, GAMESLOST) VALUES (\'" + username + "\', " + 0 + ", " + 0 + ", " + 0 + ", " + 0 + ")";
        try {
            stmt = con.createStatement();
            if (stmt.executeUpdate(query) == 1 && stmt.executeUpdate(query2) == 1 && stmt.executeUpdate(query3) == 1) {
                return true;
            }
            return false;
        } catch (SQLIntegrityConstraintViolationException e) {
            throw new SQLIntegrityConstraintViolationException();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println(e.getMessage());
            return false;
        }
    }

    public int updatePlayer(String name, String username, String password, String mail) throws SQLException, Exception {
        Statement stmt = null;
        String query = "UPDATE APP.USERS SET NAME = \'" + name + "\', PASSWORD = \'" + password + "\', EMAIL = \'" + mail + "\' WHERE USERNAME = \'" + username + "\'";
        try {
            stmt = con.createStatement();
            stmt.executeUpdate(query);
            return 1;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    public void updateScores(String winner, String loser) {
        Statement stmt = null;
        String queryWinner = "UPDATE APP.SCORES SET SCORE = SCORE+1, GAMESPLAYED = GAMESPLAYED+1, GAMESWON = GAMESWON+1 WHERE USERNAME = \'" + winner + "\'";
        String queryLoser = "UPDATE APP.SCORES SET SCORE = SCORE, GAMESPLAYED = GAMESPLAYED+1, GAMESLOST = GAMESLOST+1 WHERE USERNAME = \'" + loser + "\'";
        try {
            stmt = con.createStatement();
            stmt.executeUpdate(queryWinner);
            stmt.executeUpdate(queryLoser);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public Connection getConnection() throws SQLException {
        Connection conn = null;
        String url = "jdbc:derby://localhost:1527/BattleshipDatabase";
        try {
            Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
            conn = DriverManager.getConnection(url, USERNAME, PASSWORD);
        } catch (ClassNotFoundException e) {
            System.out.println(e.getMessage());
            System.exit(1);
        } catch (InstantiationException e) {
            System.out.println(e.getMessage());
            System.exit(1);
        } catch (IllegalAccessException e) {
            System.out.println(e.getMessage());
            System.exit(1);
        } catch (SQLNonTransientConnectionException e) {
            System.out.println(e.getMessage());
            System.exit(1);
        } catch (Exception e) {
            System.out.println(e.getMessage());
            System.exit(1);
        }
        return conn;
    }
}
